<?php
namespace app\index\model;
use think\Db;
use think\Model;

class Top_model {
	// 根据登录id查询未读消息
	public function getnews() {
		$uid = $_POST['id'];
		// var_dump($id);die;
		// 查询该id所对应的角色，组织，人员
		$oid = Db::table('cn_user_organization_relation')
			->alias('cuor')
			->join('cn_organization co', 'cuor.org_id = co.org_id')
			->where('cuor.user_id', $uid)
			->field('cuor.org_id')
			->find();
		$cid = Db::table('cn_user_character_relation')
			->alias('cucr')
			->join('cn_character cc', 'cucr.chara_id = cc.chara_id')
			->where('cucr.user_id', $uid)
			->field('cucr.chara_id')
			->find();
		// var_dump($cid);die;
		$ores = Db::table('cn_mail_addressee')
			->alias('cma')
			->join('cn_mail cm', 'cm.mail_id = cma.mail_id')
			->where('cma.add_org_id', $oid['org_id'])
			->where('cm.is_delete', 'eq', '0')
			->where('cm.is_read', 'eq', 0)
			->where('cm.mail_send_channel', 'eq', '1')
			->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
			->group('cm.mail_id')
			->select();
		$mres = Db::table('cn_mail_addressee')
			->alias('cma')
			->join('cn_mail cm', 'cm.mail_id = cma.mail_id')
			->where('cma.add_org_id', '-1')
			->where('cm.is_delete', 'eq', '0')
			->where('cm.is_read', 'eq', 0)
			->where('cm.mail_send_channel', 'eq', '1')
			->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
			->group('cm.mail_id')
			->select();
		$cres = Db::table('cn_mail_addressee')
			->alias('cma')
			->join('cn_mail cm', 'cm.mail_id = cma.mail_id')
			->where('cma.add_chara_id', $cid['chara_id'])
			->where('cm.is_delete', 'eq', '0')
			->where('cm.is_read', 'eq', 0)
			->where('cm.mail_send_channel', 'eq', '1')
			->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
			->group('cm.mail_id')
			->select();
		$ures = Db::table('cn_mail_addressee')
			->alias('cma')
			->join('cn_mail cm', 'cm.mail_id = cma.mail_id')
			->where('cma.add_user_id', $uid)
			->where('cm.is_delete', 'eq', '0')
			->where('cm.is_read', 'eq', 0)
			->where('cm.mail_send_channel', 'eq', '1')
			->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
			->group('cm.mail_id')
			->select();
		// var_dump($ores);
		// var_dump($cres);die;
		$res = array_merge_recursive($ores, $cres, $ures, $mres);
		// var_dump($res);die;
		// $data = json_encode($res);
		// var_dump($data);die;
		return $res;
	}

	// 根据登录id查询未读公告
	// 如果发了组织  角色？？？
	public function getnotices() {
		$uid = $_POST['id'];
		// var_dump($uid);die;
		$oid = Db::table('cn_user_organization_relation')
			->alias('cuor')
			->join('cn_organization co', 'cuor.org_id = co.org_id')
			->where('cuor.user_id', $uid)
			->field('cuor.org_id')
			->find();
		// var_dump($oid);die;
		$cid = Db::table('cn_user_character_relation')
			->alias('cucr')
			->join('cn_character cc', 'cucr.chara_id = cc.chara_id')
			->where('cucr.user_id', $uid)
			->field('cucr.chara_id')
			->find();
		// var_dump($cid);die;
		// $ores = Db::table('cn_mail_addressee')
		//         ->alias('cma')
		//         ->join('cn_mail cm','cm.mail_id = cma.mail_id')
		//         ->where('cma.add_org_id',$oid['org_id'])
		//         ->where('cm.is_delete','eq','0')
		//         ->where('cm.is_read','eq',0)
		//         ->where('cm.mail_send_channel','eq','2')
		//         ->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
		//         ->group('cm.mail_id')
		//         ->select();
		if ($oid['org_id']) {
			$sql = "SELECT
                    cm.mail_title,
                    cm.mail_id,
                    count( cm.mail_id ) AS count
                FROM
                    cn_mail_addressee AS cma
                    LEFT JOIN cn_mail AS cm ON cm.mail_id = cma.mail_id
                WHERE
                    cma.add_org_id = " . $oid['org_id'] . "
                    AND cm.is_delete = 0
                    AND cma.is_delete = 0
                    AND cm.is_read != 1
                    AND cm.mail_send_channel = 2
                    OR cma.add_org_id = - 1
                GROUP BY
                    cm.mail_id
                ORDER BY
                    cm.create_time DESC";
			$ores = Db::query($sql);
		} else {
			$sql = "SELECT
                    cm.mail_title,
                    cm.mail_id,
                    count( cm.mail_id ) AS count
                FROM
                    cn_mail_addressee AS cma
                    LEFT JOIN cn_mail AS cm ON cm.mail_id = cma.mail_id
                WHERE
                    cm.is_delete = 0
                    AND cma.is_delete = 0
                    AND cm.is_read != 1
                    AND cm.mail_send_channel = 2
                    OR cma.add_org_id = - 1
                GROUP BY
                    cm.mail_id
                ORDER BY
                    cm.create_time DESC";
			$ores = Db::query($sql);
		}

		// var_dump($ores);die;
		// $cres = Db::table('cn_mail_addressee')
		//         ->alias('cma')
		//         ->join('cn_mail cm','cm.mail_id = cma.mail_id')
		//         // ->where('cma.add_user_id',$uid)
		//         ->where('cma.add_chara_id',$cid['chara_id'])
		//         // ->whereOr('cma.add_chara_id',$cid)
		//         ->where('cm.is_delete','eq','0')
		//         ->where('cm.is_read','eq',0)
		//         ->where('cm.mail_send_channel','eq','2')
		//         ->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
		//         ->group('cm.mail_id')
		//         ->select();
		// $ures = Db::table('cn_mail_addressee')
		//         ->alias('cma')
		//         ->join('cn_mail cm','cm.mail_id = cma.mail_id')
		//         ->where('cma.add_user_id',$uid)
		//         ->where('cm.is_delete','eq','0')
		//         ->where('cm.is_read','eq',0)
		//         ->where('cm.mail_send_channel','eq','2')
		//         ->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
		//         ->group('cm.mail_id')
		//         ->select();
		// var_dump($ores);
		// $res = array_merge_recursive($ores,$cres,$ures,$mres);
		$res = $ores;
		// var_dump($res);die;
		// $data = json_encode($res);
		return $res;
	}

	public function getmessage() {
		$uid = $_POST['id'];
		// var_dump($uid);die;
		$oid = Db::table('cn_user_organization_relation')
			->alias('cuor')
			->join('cn_organization co', 'cuor.org_id = co.org_id')
			->where('cuor.user_id', $uid)
			->field('cuor.org_id')
			->find();
		// var_dump($oid);die;
		$cid = Db::table('cn_user_character_relation')
			->alias('cucr')
			->join('cn_character cc', 'cucr.chara_id = cc.chara_id')
			->where('cucr.user_id', $uid)
			->field('cucr.chara_id')
			->find();
		// var_dump($cid);die;
		// $ores = Db::table('cn_mail_addressee')
		//         ->alias('cma')
		//         ->join('cn_mail cm','cm.mail_id = cma.mail_id')
		//         ->where('cma.add_org_id',$oid['org_id'])
		//         ->where('cm.is_delete','eq','0')
		//         ->where('cm.is_read','eq',0)
		//         ->where('cm.mail_send_channel','eq','2')
		//         ->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
		//         ->group('cm.mail_id')
		//         ->select();
		// if ($oid['org_id']) {
		// 	$wsql = "SELECT
		//                       cm.mail_title,
		//                       cm.mail_id,
		//                       count( cm.mail_id ) AS count,
		//                       cm.mail_content,
		//                       cm.create_time,
		//                       cm.mail_id,
		//                       cms.STATUS
		//                   FROM
		//                       cn_mail AS cm
		//                       LEFT JOIN cn_mail_addressee AS cma ON cma.mail_id = cm.mail_id
		//                       LEFT JOIN cn_mail_status AS cms ON cms.mail_id = cm.mail_id
		//                   WHERE
		//                       cma.add_org_id = " . $oid['org_id'] . "

		//                       AND cm.is_delete = 0
		//                       AND cms.STATUS = 0
		//                       AND cm.mail_send_channel = 2
		//                       AND cms.user_id = " . $uid . "
		//                       and cms.status = 0
		//                   GROUP BY
		//                       cm.mail_id";
		// } else {
		$wsql = "SELECT
                        cm.mail_title,
                        cm.mail_id,
                        count( cm.mail_id ) AS count,
                        cm.mail_content,
                        cm.create_time,
                        cm.mail_id,
                        cms.STATUS
                    FROM
                        cn_mail AS cm
                        LEFT JOIN cn_mail_addressee AS cma ON cma.mail_id = cm.mail_id
                        LEFT JOIN cn_mail_status AS cms ON cms.mail_id = cm.mail_id
                    WHERE
                        cms.user_id = " . $uid . "
                        AND cm.is_delete = 0
                        AND cms.STATUS = 0
                        AND cm.mail_send_channel = 2
                        and cms.status = 0
                    GROUP BY
                        cm.mail_id";
		// }
		$ores = Db::query($wsql);

		// var_dump($ores);die;
		// $cres = Db::table('cn_mail_addressee')
		//         ->alias('cma')
		//         ->join('cn_mail cm','cm.mail_id = cma.mail_id')
		//         // ->where('cma.add_user_id',$uid)
		//         ->where('cma.add_chara_id',$cid['chara_id'])
		//         // ->whereOr('cma.add_chara_id',$cid)
		//         ->where('cm.is_delete','eq','0')
		//         ->where('cm.is_read','eq',0)
		//         ->where('cm.mail_send_channel','eq','2')
		//         ->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
		//         ->group('cm.mail_id')
		//         ->select();
		// $ures = Db::table('cn_mail_addressee')
		//         ->alias('cma')
		//         ->join('cn_mail cm','cm.mail_id = cma.mail_id')
		//         ->where('cma.add_user_id',$uid)
		//         ->where('cm.is_delete','eq','0')
		//         ->where('cm.is_read','eq',0)
		//         ->where('cm.mail_send_channel','eq','2')
		//         ->field('cm.mail_title,cm.mail_id,count(cm.mail_id)as count')
		//         ->group('cm.mail_id')
		//         ->select();
		// var_dump($ores);
		// $res = array_merge_recursive($ores,$cres,$ures,$mres);
		$res = $ores;
		// var_dump($res);die;
		// $data = json_encode($res);
		return $res;
	}
}
?>